-- Writing a Correlated Subquery
SELECT DISTINCT c.LastName, c.FirstName 
FROM Person.Person c JOIN HumanResources.Employee e
ON e.BusinessEntityID = c.BusinessEntityID
WHERE 25000.00 IN
(SELECT SalesQuota
FROM Sales.SalesPerson sp
WHERE e.BusinessEntityID = sp.BusinessEntityID);



-- Creating a Correlated Subquery with Comparison Operators
SELECT ProductID, OrderQty
FROM Sales.SalesOrderDetail s1
WHERE s1.UnitPrice <
    (SELECT AVG (s2.UnitPrice)
     FROM Sales.SalesOrderDetail s2
     WHERE s2.ProductID = s1.ProductID);
